﻿using System;
using System.Collections.Generic;
using System.Text;
using BarCode.Bean;
using BarCode.Common;
using System.Data;
using BarCode.Module;
using MySql.Data.MySqlClient;

namespace BarCode.AccessDao
{
    public class DB02Dao
    {
        private static readonly log4net.ILog LogUtil = log4net.LogManager.GetLogger(typeof(DB02Dao));

        private MySqlHelper db02;
        public DB02Dao(string serverIp, string userId, string userPwd)
        {
            db02 = new MySqlHelper(serverIp, "db_02", userId, userPwd);
        }

        /// <summary>
        /// 判断是否可链接
        /// </summary>
        /// <returns></returns>
        public bool EnableConnect()
        {
            return db02.EnableConnect();
        }

        /// <summary>
        /// 表号检查
        /// </summary>
        /// <returns> 错误信息 </returns>
        public string checkBH(string barcode)
        {
            MySqlParameter sqlParam = new MySqlParameter("@ZCH", barcode);
            DataSet retSet = db02.GetDataSet(CommandType.Text, "select JL from ZhongJian where ZCH = @ZCH order by CQSJ desc ", new MySqlParameter[] { sqlParam });
            if (retSet != null && retSet.Tables.Count > 0)
            {
                DataTable dt = retSet.Tables[0];
                if (dt.Rows.Count > 0)
                {
                    string JL = Convert.ToString(dt.Rows[0][0]);
                    if (!"合格".Equals(JL))
                    {
                        return "表号不合格";
                    } else 
                    {
                        return "";
                    }

                }
                else
                {
                    return "表号不存在";
                }
            }
            else
            {
                return "表号不存在";
            }
        }

        /// <summary>
        /// 获取Calibrations信息
        /// </summary>
        /// <returns>  </returns>
        public Calibrations selectCalibrations(string jkNo)
        {
            MySqlParameter sqlParam = new MySqlParameter("@jkNo", jkNo);
            DataSet retSet = db02.GetDataSet(CommandType.Text, "SELECT c.Program, m.MeterData10,c.CalibrationId, c.EquipmentId , c.notes FROM Calibrations c inner join Meters m on c.CalibrationId = m.CalibrationId and c.EquipmentId = m.EquipmentId  where m.Jk_Number = @jkNo order by c.Date desc limit 0,1", new MySqlParameter[] { sqlParam });
            if (retSet != null && retSet.Tables.Count > 0)
            {
                DataTable dt = retSet.Tables[0];
                if (dt.Rows.Count > 0)
                {
                    DataRow tmpRow = dt.Rows[0];
                    Calibrations retInfo = new Calibrations();
                    retInfo.Program = tmpRow["Program"].ToString();
                    string meterDirect = tmpRow["MeterData10"].ToString();
                    string model = tmpRow["Notes"].ToString();
                    LogUtil.Info("Model：" + tmpRow["Notes"]);
                    if ("1".Equals(meterDirect))
                    {
                        retInfo.MeterData10 = "左";
                    }
                    else if ("2".Equals(meterDirect))
                    {
                        retInfo.MeterData10 = "右";
                    }
                    else
                    {
                        retInfo.MeterData10 = "";
                    }
                    //string st = tmpRow["Notes"].ToString().SubString(0, tmpRow["Notes"].ToString().length-1);
                    LogUtil.Info("型号：" + tmpRow["Notes"]);
                    retInfo.Notes = model;
                    retInfo.CalibrationId = Convert.ToInt32(tmpRow["CalibrationId"]);
                    retInfo.EquipmentId = tmpRow["EquipmentId"].ToString();
                    return retInfo;
                }
                
            }
            return null;
        }

        /// <summary>
        /// 是否合格
        /// </summary>
        /// <returns>  </returns>
        public string checkF(string calibrationId,string equipmentId)
        {
            MySqlParameter[] sqlParams = new MySqlParameter[2];
            sqlParams[0] = new MySqlParameter("@calibration_id", calibrationId);
            sqlParams[1] = new MySqlParameter("@equipment_id", equipmentId);
            DataSet retSet = db02.GetDataSet(CommandType.Text, "select m.MeterAccepted from Meters m where m.CalibrationId = @calibration_id and m.EquipmentId = @equipment_id ", sqlParams);
            LogUtil.Info("是否合格" + retSet.Tables[0].Rows[0]["MeterAccepted"]);
            string s = retSet.Tables[0].Rows[0]["MeterAccepted"]+ "";
                return s;
            
        }


        /// <summary>
        /// 更新保存表号
        /// </summary>
        /// <param name="lstBarCode"></param>
        /// <returns>处理结果</returns>
        public int updateMeterNo(Calibrations calibrationsInfo, string meterNo, string jkNo)
        {

            int updCnt = 0;
            try
            {
                string updSql = "update Meters set SerialNumber=@meterNo, MeterData5=@OptUser, MeterData6=@OptDate where CalibrationId=@CalibrationId and EquipmentId=@EquipmentId and Jk_Number=@Jk_Number";

                MySqlParameter[] sqlParams = new MySqlParameter[6];
                sqlParams[0] = new MySqlParameter("@meterNo", meterNo);
                sqlParams[1] = new MySqlParameter("@OptUser", GlobalVal.CurrentUser.User);
                sqlParams[2] = new MySqlParameter("@OptDate", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                sqlParams[3] = new MySqlParameter("@CalibrationId", calibrationsInfo.CalibrationId);
                sqlParams[4] = new MySqlParameter("@EquipmentId", calibrationsInfo.EquipmentId);
                sqlParams[5] = new MySqlParameter("@Jk_Number", jkNo);
                updCnt = db02.ExecuteNonQuery(CommandType.Text, updSql, sqlParams);
                LogUtil.Info("表号更新：" + updCnt);

            }
            catch (Exception ex)
            {
                LogUtil.Error("表号更新异常", ex);
                
            }

            return updCnt;
        }


        /// <summary>
        /// 获取指定用户当日的表号关联工作量
        /// </summary>
        /// <param name="userName"></param>
        /// <returns></returns>
        public int getRelateWorkAmount(string userName)
        {
            MySqlParameter param = new MySqlParameter("@Operator_Name", userName);
            string amountSql = "select count(1) from Meters where MeterData5=@Operator_Name ";

            string getSysDtSql = "select DATE_FORMAT(date_add(now(), interval " + GlobalVal.Diff_Hour + " hour),'%H%i%s')";
            string strSysTime = Convert.ToString(db02.ExecuteScalar(getSysDtSql));
            LogUtil.Info("获取服务器系统时间：" + strSysTime);


            if (strSysTime.CompareTo(GlobalVal.WORK_START_TIME) < 0)
            {
                //amountSql += " AND DATE_FORMAT(OpDt,'%Y%m%d%H%i%s') >= DATE_FORMAT(date_add(now(), interval " + (GlobalVal.Diff_Hour - 24) + " hour), '%Y%m%d" + GlobalVal.WORK_START_TIME + "')";
                //amountSql += " AND DATE_FORMAT(OpDt,'%Y%m%d%H%i%s') < DATE_FORMAT(date_add(now(), interval " + GlobalVal.Diff_Hour + " hour), '%Y%m%d" + GlobalVal.WORK_START_TIME + "')";
                DateTime startDate = DateTime.Now.AddHours(GlobalVal.Diff_Hour - 24);
                string startDateStr = startDate.ToString("yyyy-MM-dd ") + GlobalVal.WORK_START_TIME;
                DateTime endDate = DateTime.Now.AddHours(GlobalVal.Diff_Hour);
                string endDateStr = endDate.ToString("yyyy-MM-dd ") + GlobalVal.WORK_START_TIME;

                amountSql += " AND MeterData6 between '" + startDateStr + "' and '" + endDateStr + "'";

            }
            else
            {
                ////amountSql += " AND DATE_FORMAT(OpDt,'%Y%m%d%H%i%s') >= DATE_FORMAT(date_add(now(), interval " + GlobalVal.Diff_Hour + " hour), '%Y%m%d" + GlobalVal.WORK_START_TIME + "')";
                ////amountSql += " AND DATE_FORMAT(OpDt,'%Y%m%d%H%i%s') < DATE_FORMAT(date_add(now(), interval " + (GlobalVal.Diff_Hour + 24) + " hour), '%Y%m%d" + GlobalVal.WORK_START_TIME + "')";
                //amountSql += " AND OpDt between str_to_date(DATE_FORMAT(date_add(now(), interval " + GlobalVal.Diff_Hour + " hour), '%Y%m%d" + GlobalVal.WORK_START_TIME + "'),'%Y%m%d%H%i%s') ";
                //amountSql += "          and str_to_date(DATE_FORMAT(date_add(now(), interval " + (GlobalVal.Diff_Hour + 24) + " hour), '%Y%m%d" + GlobalVal.WORK_START_TIME + "'),'%Y%m%d%H%i%s') ";

                DateTime startDate = DateTime.Now.AddHours(GlobalVal.Diff_Hour);
                string startDateStr = startDate.ToString("yyyy-MM-dd ") + GlobalVal.WORK_START_TIME;
                DateTime endDate = DateTime.Now.AddHours(GlobalVal.Diff_Hour + 24);
                string endDateStr = endDate.ToString("yyyy-MM-dd ") + GlobalVal.WORK_START_TIME;

                amountSql += " AND MeterData6 between '" + startDateStr + "' and '" + endDateStr + "'";

            }
            LogUtil.Info("获取表号关联工作量SQL：" + amountSql);
            object amount = db02.ExecuteScalar(CommandType.Text, amountSql, new MySqlParameter[] { param });
            return Convert.ToInt32(amount);
        }
    }
}
